Deadlock graph using WMI(Windows Management Instrumentaion):
WMI event alert:-
This fires an event in response to a Windows Management Instrumentation (WMI) event, such as when a new file appears in a specific folder.
Advantages of deadlock graph:
1. It is in readable format.
2. The graph actually attached with mail.
3. No unnecessary mail triggered.
4. Deadlock information is live scenario base.
5. Previous deadlock entries getting stored in dba database.
6. It detects deadlock information from default trace, no trace flag 1204 and 1222 is not required.
Email:
Graph:
Configuration:
/*Requires sysadmin permission to create and execute this job*/
create database dbaalert
go
use dbaalert
go
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Deadlock]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
Print ' *** IF Table Deadlock Table Already Exists... moving Further *** '
ELSE
create table Deadlock
(
RecordId int identity (1,1) primary key not null,
AlertTime datetime not null,
DeadlockGraph xml,
Notified int not null constraint [DF_deadlock_flag] default (0)
)
go
create index deadlock_idx on Deadlock (AlertTime) with fillfactor = 100
go
USE [msdb]
GO
EXEC msdb.dbo.sp_set_sqlagent_properties @alert_replace_runtime_tokens=1
GO
USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'DBA Group - monitoring - Deadlock detector',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'
Funtionality:
1. Real time Deadlock detection using WMI
2. Send email out when deadlock occurs along with Deadlock Graph attached.
3. Logs all the deadlocks in a central Table
dbamaint.dbo.Deadlock
4. Does not require trace flags 1204 and 1222 enabled',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Insert Deadlock info',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'INSERT INTO Deadlock (
AlertTime,
DeadlockGraph
)
VALUES (
GETDATE(),
''$(ESCAPE_NONE(WMI(TextData)))''
)',
@database_name=N'dbaalert',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Send Email with Deadlock Graph',
@step_id=2,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'if exists (select 1 from dbo.Deadlock where notified = 0 )
begin
declare @tableHTML nvarchar(max)
set @tableHTML =N''
Deadlock Has occured on ''+@@servername+'' ..
''
set @tableHTML = @tableHTML+ N'''' +
N'''' +
N'''' +
N'''' +
N'''' +
N'''' +
ISNULL(CAST ( (
select td = '''',
td = RecordId,'''',
td = AlertTime,'''',
td = DeadlockGraph,''''
from dbo.Deadlock where notified = 0
FOR XML PATH(''tr''), TYPE
) AS NVARCHAR(MAX) ),'''') +
N'''' +
N''
RecordId AlertTime DeadlockGraph
'' ;
exec master..xp_cmdshell 'bcp "SELECT [Deadlockgraph].query(''/TextData/deadlock-list'') FROM dbaalert..[Deadlock] where Notified = 0" queryout "c:\deadlock_test\deadlock.xdl" -c -q -T -S MBISQLVP1';
-- send email out with the graph attached
declare @subject1 varchar(50)
set @subject1 = ''Deadlock Has Occured on ''+@@servername
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ''DBASUPPORT_profile'',
@recipients=''dbasupport@manipalcigna.com'',
@subject = @subject1,
@body = @tableHTML,
@body_format = ''HTML'',
@file_attachments = ''c:\deadlock\deadlock.xdl'';
end
go
-- update the Deadlock table so that when the job runs it wont send out previous alert
update dbaalert.dbo.Deadlock
set Notified = 1 where notified = 0
--SELECT * FROM dbaalert.dbo.Deadlock',
@database_name=N'dbaalert',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
-- Create an WMI alert to respond to deadlocks
IF EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Respond to Deadlock')
EXEC msdb.dbo.sp_delete_alert @name=N'Respond to Deadlock'
GO
DECLARE @server_namespace varchar(255)
IF ISNULL(CHARINDEX('\', @@SERVERNAME), 0) > 0
SET @server_namespace = N'\\.\root\Microsoft\SqlServer\ServerEvents\' + SUBSTRING(@@SERVERNAME, ISNULL(CHARINDEX('\', @@SERVERNAME), 0) + 1, LEN(@@SERVERNAME) - ISNULL(CHARINDEX('/', @@SERVERNAME), 0))
ELSE
SET @server_namespace = N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER'
EXEC msdb.dbo.sp_add_alert @name=N'Respond to Deadlock',
@wmi_namespace=@server_namespace,
@wmi_query=N'SELECT * FROM DEADLOCK_GRAPH',
@job_name='DBA Group - monitoring - Deadlock detector' ;
--Two global temp tables with sample data for demo purposes.
CREATE TABLE Employees (
EmpId INT IDENTITY,
EmpName VARCHAR(16),
Phone VARCHAR(16)
)
GO
INSERT INTO Employees (EmpName, Phone)
VALUES ('Martha', '800-555-1212'), ('Jimmy', '619-555-8080')
GO
CREATE TABLE Suppliers(
SupplierId INT IDENTITY,
SupplierName VARCHAR(64),
Fax VARCHAR(16)
)
GO
INSERT INTO Suppliers (SupplierName, Fax)
VALUES ('Acme', '877-555-6060'), ('Rockwell', '800-257-1234')
GO
Session 1 | Session 2
========================================
BEGIN TRAN; | BEGIN TRAN;
========================================
UPDATE Employees
SET EmpName = 'Mary'
WHERE EmpId = 1
========================================
| UPDATE Suppliers
| SET Fax = N'555-1212'
| WHERE SupplierId = 1
========================================
UPDATE Suppliers
SET Fax = N'555-1212'
WHERE SupplierId = 1
========================================
| UPDATE Employees
| SET Phone = N'555-9999'
| WHERE EmpId = 1
========================================
|
========================================